package org.jeecg.modules.employee.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.employee.entity.Employee;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * @Description: 员工信息表
 * @Author: jeecg-boot
 * @Date:   2025-01-16
 * @Version: V1.0
 */
public interface EmployeeMapper extends BaseMapper<Employee> {

    @Select("select count(*) as value, s.depart_name as name " +
            "from employee e left join sys_depart s on e.dept = s.id " +
            "where s.depart_name is not null " +
            "group by e.dept;")
    List<Map<String, Object>> getCenter1();

    @Select("SELECT m.month, COALESCE(create_counts.count_type, 0) AS create_count, " +
            "COALESCE(disable_counts.count_type, 0) AS update_and_disable_count " +
            "FROM (SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) AS m " +
            "LEFT JOIN (SELECT MONTH(create_time) AS month, COUNT(*) AS count_type FROM employee WHERE create_time >= DATE_FORMAT(CURRENT_DATE(), '%Y-01-01') AND create_time < DATE_FORMAT(CURRENT_DATE() + INTERVAL 1 YEAR, '%Y-01-01') GROUP BY MONTH(create_time)) AS create_counts ON m.month = create_counts.month " +
            "LEFT JOIN (SELECT MONTH(update_time) AS month, COUNT(*) AS count_type FROM employee WHERE disable = 1 AND update_time >= DATE_FORMAT(CURRENT_DATE(), '%Y-01-01') AND update_time < DATE_FORMAT(CURRENT_DATE() + INTERVAL 1 YEAR, '%Y-01-01') GROUP BY MONTH(update_time)) AS disable_counts ON m.month = disable_counts.month " +
            "ORDER BY m.month;")
    List<Map<String, Object>> getLeft3();

    @Select("SELECT e.id, sd.depart_name as dept, ssd.depart_name as classes " +
            "FROM employee e " +
            "LEFT JOIN sys_depart sd ON e.dept = sd.id " +
            "LEFT JOIN sys_depart ssd on ssd.id = e.classes " +
            "WHERE e.disable = 0")
    List<Employee> deptNameList();

}
